if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_pu_tong')
          and type in ('P','PC'))
   drop procedure bd_proc_schedule_pu_tong
go


create procedure bd_proc_schedule_pu_tong (@user_serial_str varchar(max),@begin_date date,@end_date date,@ban_ci varchar(max),@ip varchar(max),@operator varchar(max)) as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;


-- 定义返回受影响行数结果
	declare @result int = 0;


	--  异常扑捉机制
		BEGIN TRY

			CREATE TABLE #user_serial(
				id INT IDENTITY,
				user_serial VARCHAR(20) COLLATE Chinese_PRC_CI_AS
			)

			CREATE TABLE #result(
			  user_no varchar(200) COLLATE Chinese_PRC_CI_AS,
			  user_name varchar(200) COLLATE Chinese_PRC_CI_AS,
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  dept_name varchar(200) COLLATE Chinese_PRC_CI_AS,
			  dept_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  col_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
			  value VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
			  date DATE,
			  year_month VARCHAR(10) COLLATE Chinese_PRC_CI_AS,
			  shift_is_rest int
			)

	    --创建 返回不满足排班的人的原因
			create table #return_table(
				user_serial VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				user_no VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				dept_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS
			)

			CREATE TABLE #result_insert(
			  user_serial  INT,
			  col_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
			  value VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
			  date DATE,
			  year_month VARCHAR(10) COLLATE Chinese_PRC_CI_AS
			)

			CREATE TABLE #result_update(
			  user_serial  INT,
			  col_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
			  value VARCHAR(100)COLLATE Chinese_PRC_CI_AS,
			  date DATE,
			  year_month VARCHAR(10) COLLATE Chinese_PRC_CI_AS
			)


			CREATE TABLE #kt_paiba (
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  rq nvarchar(10) COLLATE Chinese_PRC_CI_AS,
			  d1 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d2 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d3 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d4 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d5 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d6 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d7 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d8 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d9 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d10 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d11 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d12 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d13 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d14 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d15 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d16 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d17 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d18 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d19 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d20 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d21 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d22 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d23 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d24 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d25 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d26 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d27 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d28 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d29 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d30 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d31 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  gly_no nvarchar(10) COLLATE Chinese_PRC_CI_AS,
			  year int ,
			  month int
			)


		-- 开启事务
			BEGIN TRAN


				-- 拆分用户编号保存临时表中
				insert into #user_serial
				select * from string_split(iif(isnull(@user_serial_str,'')='','',@user_serial_str),',');


				-- 查询班次保存临时表中(这里普通班次默认都是工作日)
				declare @ban_ci_name varchar(50);
				select @ban_ci_name = concat('[A]',isnull(bh,''),'|',isnull(mc,'')) FROM zt_banci t WHERE bh = @ban_ci


				-- select * from A,B 不指定条件那么会产生A的所有B数据，刚好满足需求
				INSERT INTO #result (user_serial, col_name, value, date, year_month)
				select t1.user_serial,t2.*
				from (select * from #user_serial) t1,
				(
					SELECT 'd'+CAST(d.date_day AS VARCHAR(2)) colName, @ban_ci_name as name,d.date,CONVERT(VARCHAR(7),date,120) date_month
					FROM bd_sys_dim_date d
					WHERE date >= @begin_date AND date <= @end_date
				) t2

				declare @shift_is_rest int;
				set @shift_is_rest = iif(left(isnull(@ban_ci_name,''),3) = '[X]',1,0)
        update #result set shift_is_rest = iif(left(isnull(value,''),3) = '[X]',1,0)

        -- 更新是否为休息日(一定要在更新kt_paiba表前，删除bd_kq_schedule_detail表数据前)
        update a set a.shift_is_rest = 1,a.value = concat('[X]',substring(a.value,4,len(a.value)))
        from #result a
        inner join (
          -- 两个集合总和的rest大于1 说明一定要是休息日(普通班次)
          select a.user_serial,a.date
          from #result a
          inner join bd_kq_schedule_detail b on a.date = b.shift_date and a.user_serial = b.user_serial
          where (isnull(a.shift_is_rest,0) + isnull(b.shift_is_rest,0)) >= 1
        ) b on a.user_serial = b.user_serial and a.date = b.date


        -- 更新人员信息
        update a set a.user_no = isnull(b.user_no,''), a.user_name = isnull(b.user_lname,''), a.dept_name = isnull(c.dep_name,''), a.dept_serial = isnull(c.dep_serial,'')
        from #result a
        left join dt_user b on a.user_serial = b.user_serial
        left join dt_dep c on b.user_dep = c.dep_serial

        -- 检测是否为封账状态
        insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
        select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,concat(@begin_date,' —— ',@end_date,' 该时段考勤存在已经封账,不能排班')
        from #result a
        inner join bd_kq_accounts c on a.user_serial = c.user_serial and year(a.date) = c.year and month(a.date) = c.month

        -- 删除已经封账的数据
        delete a
        from #result a
        inner join #return_table b on a.user_serial = b.user_serial

				-- 检测非弹性人员排弹性班次
-- 				DECLARE @isTx int = 0
-- 				select @isTx = is_flextime from zt_shid where parent = @ban_ci;
-- 				if(@isTx=1)
-- 						begin
-- 							insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
-- 							select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,concat(@begin_date,' —— ',@end_date,'非弹性人员不能排弹性班次')
-- 							from #result a
-- 							inner join dt_user c on a.user_serial = c.user_serial
-- 							where isnull(c.kqdkLx,0) != 21;
--
-- 							-- 删除非弹性人员排弹性班次
-- 							delete a
-- 							from #result a
-- 							inner join #return_table b on a.user_serial = b.user_serial;
--
-- 						end

				-- 需要新增的数据 #result_insert
				insert into #result_insert(user_serial,col_name,value,date,year_month)
				select a.user_serial,a.col_name,a.value,a.date,a.year_month
				from #result a
				inner join (
					select user_serial,convert(varchar(7),date,120) year_month from #result
					except
					select user_serial,rq as year_month from kt_paiba
				) b on a.user_serial = b.user_serial and a.year_month = b.year_month


				-- 需要修改的数据 #result_update
				insert into #result_update(user_serial,col_name,value,date,year_month)
				select a.user_serial,a.col_name,a.value,a.date,a.year_month
				from #result a
				inner join (
					select user_serial,convert(varchar(7),date,120) year_month from #result
					Intersect
					select user_serial,rq as year_month from kt_paiba
				) b on a.user_serial = b.user_serial and a.year_month = b.year_month


				-- 将插入的数据存入到临时排班表中
				-- 下面两段代码将竖着的数据转为了横着的数据
				INSERT INTO #kt_paiba (user_serial, rq, gly_no, year, month)
				SELECT distinct a.user_serial,CONVERT(VARCHAR(7),date,120),@operator,YEAR(date),MONTH(date)
				FROM #result_insert a


				-- 循环拼接需要更新临时表中的字段
				-- 查询（循环）拼接数据（竖着循环）
				DECLARE @update_insert VARCHAR(MAX)=''
				SELECT @update_insert+='UPDATE #kt_paiba SET '+col_name+'='''+isnull(value,'')+''',gly_no = '''+@operator+''' WHERE rq='''+year_month+''' AND user_serial='+CAST(a.user_serial AS VARCHAR(10))+';'
				FROM #result_insert a

				-- 执行拼接的语句
				EXEC(@update_insert)
				set @result = @result + @@ROWCOUNT;

				-- 从临时表插入到真实表中
				insert into kt_paiba
				select * from #kt_paiba

				-- 直接修改kt_paiba排班（那些存在的数据）
				DECLARE @update_update VARCHAR(MAX)=''
				SELECT @update_update+='UPDATE kt_paiba SET '+col_name+'='''+isnull(value,'')+''',gly_no = '''+@operator+''' WHERE rq='''+year_month+''' AND user_serial='+CAST(user_serial AS VARCHAR(10))+';'
				FROM #result_update a

				-- 执行拼接的SQL
				EXEC(@update_update)
				set @result = @result + @@ROWCOUNT;

				-- 删除数据详情表
				delete a
				from bd_kq_schedule_detail a
				inner join #result b on a.user_serial = b.user_serial and a.shift_date = b.date


				-- 插入数据详情表
				insert into bd_kq_schedule_detail(user_serial,shift_id,shift_name,shift_date,shift_begin,shift_end,shift_sign_begin,shift_sign_end,shift_late,shift_early,shift_duty_on_delay,shift_duty_off_delay,shift_workday,shift_workday_hours,is_enjoyment_subsidized,middle_shift_start_time,middle_shift_end_time,night_shift_start_time,night_shift_end_time,repast_start_time,repast_end_time,repast_start_time2,repast_end_time2,shift_is_rest)
				select a.user_serial,b.id,a.value,a.date,isnull(c.sbsj,0),isnull(c.xbsj,0),isnull(c.sbks,0),isnull(c.xbjs,0),isnull(c.cdsj,0),isnull(c.ztsj,0),isnull(c.bjcd,0),isnull(c.bjzt,0),isnull(c.alld,0),isnull(c.allt,0),isnull(c.is_enjoyment_subsidized,0),isnull(c.middle_shift_start_time,0),isnull(c.middle_shift_end_time,0),isnull(c.night_shift_start_time,0),isnull(c.night_shift_end_time,0),isnull(repast_start_time,0),isnull(c.repast_end_time,0),isnull(repast_start_time2,0),isnull(c.repast_end_time2,0),a.shift_is_rest
				from #result a,zt_banci b,zt_shid c
				where b.bh = c.parent and b.bh = @ban_ci and c.lx = 0


				-- 新增的生成日志记录
				insert into wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)
				select distinct 13,1,isnull(@operator,'')+'录入'+isnull(b.user_lname,'')+isnull(a.year_month,'')+'排班',isnull(@operator,''),getdate(),'',isnull(@ip,''),''
				from #result_insert a
				inner join dt_user b on a.user_serial = b.user_serial


				--更新的生成日志记录
				insert into wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)
				select distinct 13,1,isnull(@operator,'')+'更新'+isnull(b.user_lname,'')+isnull(a.year_month,'')+'排班',isnull(@operator,''),getdate(),'',isnull(@ip,''),''
				from #result_update a
				inner join dt_user b on a.user_serial = b.user_serial

				--返回
				select * from #return_table;

        -- 调用考勤计算(排班4)
        EXEC bd_proc_timecard_extend @begin_date,@end_date,@user_serial_str,4

		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
			ROLLBACK TRAN;
		END CATCH



	

--  清除临时表
	drop table #user_serial
	drop table #kt_paiba
	drop table #result
	drop table #result_insert
	drop table #result_update
	drop table #return_table


-- 打开打印受影响行数
SET NOCOUNT OFF;
END
go
